﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Text;

namespace RedemptoristRetreatCenter.DatabaseUtility
{
    public class RetreatUtility
    {
        private SqlConnection rrcDbConn;

        public RetreatUtility()
        {
            rrcDbConn = new SqlConnection();
            rrcDbConn.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        }

        public void openConnection()
            {
                try
                {
                    rrcDbConn.Open();
                    Console.WriteLine(Environment.NewLine + "rrcDbConn.State: " + rrcDbConn.State.ToString());

                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error opening connecdtion: " + ex.ToString());
                }
            }

        public void closeConnection()
        {
            rrcDbConn.Close();
            Console.WriteLine("rrcDbConn.State: " + rrcDbConn.State.ToString());
        }

        /// <summary>
        /// Retrieve all active (non-deleted) userAccounts
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<RetreatInfo> getAllRetreats()
        {
            StringBuilder cmdText = new StringBuilder();
            cmdText.Append("SELECT r.retreatId, r.title, r.description, r.duration, r.fee, c.shortDescription, c.categoryId ");
            cmdText.Append("FROM Retreat r left join RetreatCategory c on r.categoryId=c.categoryId ");
            cmdText.Append("WHERE r.activeData=1");

            SqlCommand sqlCmd = new SqlCommand(cmdText.ToString(), rrcDbConn);

            return retrieveRetreats(sqlCmd);
        }


        /// <summary>
        /// retrieve the userAccounts that match input retreate id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public RetreatInfo getRetreatById(int targetId)
        {
            StringBuilder cmdText = new StringBuilder();
            cmdText.Append("SELECT r.retreatId, r.title, r.description, r.duration, r.fee, c.shortDescription, c.categoryId ");
            cmdText.Append("FROM Retreat r left join RetreatCategory c on r.categoryId=c.categoryId ");
            cmdText.Append(String.Format("WHERE r.activeData=1 AND r.retreatId={0};", targetId));

            SqlCommand sqlCmd = new SqlCommand(cmdText.ToString(), rrcDbConn);

            List<RetreatInfo> retreats = retrieveRetreats(sqlCmd);

            if (retreats != null && retreats.Count > 0)
            {
                return retreats[0];  // since retreatTable id is unique, only one row will be returned
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// retrieve userAccounts where title is like parameter
        /// </summary>
        /// <param name="targetTitle"></param>
        /// <returns></returns>
        public List<RetreatInfo> getRetreatByTitle(string targetTitle)
        {
            StringBuilder cmdText = new StringBuilder();
            cmdText.Append("SELECT r.retreatId, r.title, r.description, r.duration, r.fee, c.shortDescription, c.categoryId ");
            cmdText.Append("FROM Retreat r left join RetreatCategory c on r.categoryId=c.categoryId ");
            cmdText.Append(String.Format("WHERE r.activeData=1 AND r.title like '%{0}%';", targetTitle));

            SqlCommand sqlCmd = new SqlCommand(cmdText.ToString(), rrcDbConn);

            return retrieveRetreats(sqlCmd);
        }

        /// <summary>
        /// retrieve userAccounts where catagory text is like parameter
        /// </summary>
        /// <param name="targetCategory"></param>
        /// <returns></returns>
        public List<RetreatInfo> getRetreatByCategory(string targetCategory)
        {
            SqlCommand cmdText = new SqlCommand(String.Format("SELECT r.retreatId, r.title, r.description, r.duration, c.shortDescription as catagoryDesc, c.categoryId " +
                "FROM Retreat r inner join RetreatCategory c on r.categoryId=c.categoryId " +
                "WHERE c.shortDescription like %{0}% ORDER BY r.title;", targetCategory), rrcDbConn);

            return retrieveRetreats(cmdText);
        }

        private List<RetreatInfo> retrieveRetreats(SqlCommand sqlCmd)
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            SqlDataReader retreatReader = null;
            retreatReader = sqlCmd.ExecuteReader();
            RetreatInfo retreatInfo = null;
            List<RetreatInfo> retreats = new List<RetreatInfo>();


            while (retreatReader.Read())
            {
                retreatInfo = new RetreatInfo();
                retreatInfo.retreatId = (int)retreatReader["retreatId"];
                retreatInfo.title = retreatReader["title"].ToString();
                retreatInfo.description = retreatReader["description"].ToString();
                retreatInfo.duration = (double)retreatReader["duration"];
                retreatInfo.fee = (double)retreatReader["fee"];
                retreatInfo.categoryDesc = retreatReader["shortDescription"].ToString();
                retreatInfo.categoryId = (int)retreatReader["categoryId"];
                retreats.Add(retreatInfo);

            }
            closeConnection();
            return retreats;
        }

        /// <summary>
        /// Insert new retreat item into database
        /// Returns true row added to db.
        /// </summary>
        /// <param name="retreatInfo"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public bool insertRetreat( RetreatInfo retreatInfo, string username) 
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            int rowsAffected = 0;
                
            try
            {
                string insertText = String.Format("INSERT INTO Retreat (title, description, duration, fee, categoryId, modifiedBy, modifiedTimestamp, activeData) " +
                        "Values ('{0}','{1}',{2},{3},{4},'{5}','{6}', 1);", retreatInfo.title, retreatInfo.description, retreatInfo.duration, retreatInfo.fee, retreatInfo.categoryId, username, DateTime.Now);
                    SqlCommand sqlCmd = new SqlCommand(insertText, rrcDbConn);

                    rowsAffected = sqlCmd.ExecuteNonQuery();
                    
            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to insert.  exception=" + ex.ToString());
                rowsAffected = -1;
            }
            finally
            {
                closeConnection();
            }

            return (rowsAffected > 0);
        }

    /// <summary>
    /// Update a retreat to the new values passed in
    /// </summary>
    /// <param name="retreatCategoryInfo"></param>
    /// <param name="username"></param>
    /// <returns></returns>
        public bool updateRetreat(RetreatInfo retreatInfo, string username)
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            int rowsAffected = 0;

            try
            {
                string cmdText = String.Format("UPDATE Retreat SET(title='{0}', description='{1}', duration={2}, fee={3}, categoryId={4}, modifiedBy='{5}', modifiedTimestamp='{6}') " +
                    "WHERE retreatId={7};", retreatInfo.title, retreatInfo.description, retreatInfo.duration, retreatInfo.fee, retreatInfo.categoryId, username, DateTime.Now,retreatInfo.retreatId);
                SqlCommand sqlCmd = new SqlCommand(cmdText, rrcDbConn);

                rowsAffected = sqlCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to update.  exception=" + ex.ToString());
                rowsAffected = -1;
            }
            finally
            {
                closeConnection();
            }

            return (rowsAffected > 0);
        }

    /// <summary>
    /// Delete retreat whose id matches the id passed in
    /// Note: The delete method used is a Soft Delete, meaning the data row will be marked inactive and not included in any retreat retrieval methods
    /// </summary>
    /// <param name="retreatId"></param>
    /// <param name="username"></param>
    /// <returns></returns>
        public bool deleteRetreat(int retreatId, string username)
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            int rowsAffected = 0;

            try
            {
                string insertText = String.Format("UPDATE Retreat SET activeData=0, modifiedBy='{0}', modifiedTimestamp='{1}' WHERE retreatId={2};", username, DateTime.Now, retreatId);
                SqlCommand sqlCmd = new SqlCommand(insertText, rrcDbConn);

                rowsAffected = sqlCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to update.  exception=" + ex.ToString());
                rowsAffected = -1;
            }
            finally
            {
                closeConnection();
            }

            return (rowsAffected > 0);
        }

        public List<RetreatCategoryInfo> getAllRetreatCategories()
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            SqlCommand cmdText = new SqlCommand("SELECT c.categoryId, c.shortDescription, c.longDescription " +
                                                "FROM RetreatCategory c WHERE c.activeData=1 ORDER BY c.shortDescription;", rrcDbConn);

  
            SqlDataReader retreatReader = null;
            retreatReader = cmdText.ExecuteReader();
            RetreatCategoryInfo retreatCategoryInfo = null;
            List<RetreatCategoryInfo> categories = new List<RetreatCategoryInfo>();

            while (retreatReader.Read())
            {
                retreatCategoryInfo = new RetreatCategoryInfo();
                retreatCategoryInfo.categoryId = (int)retreatReader["categoryId"];
                retreatCategoryInfo.shortDescription = retreatReader["shortDescription"].ToString();
                retreatCategoryInfo.longDescription = retreatReader["longDescription"].ToString();

                categories.Add(retreatCategoryInfo);

            }
            closeConnection();
            return categories;
        }
    }
}